set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_design_vehicle_ontime_detail_dt
select
    ship.datetime --运行日期
     ,ship.shipment_no --任务单号
     ,ship.vehicleline_name --线路名称
     ,ship.vehicleline_code--线路编码
     ,ship.line_name --线路段
     ,ship.total_mileage --线路段公里数
     ,ship.task_type --任务类型
     ,ship.operation_model --运行模式
     ,ship.plate_number --车牌号
     ,ship.vehicletype_name --车型
     ,ship.planned_departure_time --规划发车时间
     ,ship.actual_departure_time --实际发车时间
     ,ship.ispunctuality --是否准点
     ,ship.planned_arrival_time --规划到达时间
     ,ship.actual_arrival_time --实际到达时间
     ,ship.islate --是否晚点
     ,round(ship.plan_driving_time,0) --规划运行时长
     ,round(ship.actual_driving_time,0) --实际运行时长
     ,case when ship.actual_driving_time >  ship.plan_driving_time then '是'
              when ship.actual_driving_time <= ship.plan_driving_time then '否' end as istimeout --是否超时
     ,round((ship.actual_driving_time - ship.plan_driving_time),0) as timevalue --时长差值实际减规划
     ,ship.start_center_name as start_center_name --出发转运中心名称+编号
     ,provider.id as start_center_id --出发转运中心id
     ,ship.load_site_code as start_center_code --出发转运中心编号
     ,ship.load_site_name as start_center --出发转运中心名称
     ,regional.financial_center_id as start_agent_id --出发代理区id
     ,regional.financial_center_code as start_agent_code --出发代理区编码
     ,regional.financial_center_desc as start_agent_name --出发代理区名称
     ,regional.regional_id as start_regional_id --始发大区id
     ,regional.regional_desc as start_regional_desc --始发大区名称
     ,regional.provider_id as start_provider_id  --出发省id
     ,regional.provider_desc as start_provider_name --出发省名称
     ,ship.end_center_name as end_center_name --目的转运中心名称+编号
     ,provider.id as end_center_id --目的转运中心id
     ,ship.next_station_code as end_center_code --目的转运中心编号
     ,ship.next_station_name as end_center --目的转运中心名称
     ,provider.financial_center_id as end_agent_id --目的代理区id
     ,provider.financial_center_code as end_agent_code --目的代理区编码
     ,provider.financial_center_desc as end_agent_name --目的代理区名称
     ,provider.regional_id as end_regional_id --始发大区id
     ,provider.regional_desc as end_regional_desc --始发大区名称
     ,provider.provider_id as end_provider_id --目的省id
     ,provider.provider_desc as end_provider_name --目的省名称
     ,to_date(ship.datetime) as dt
from(
        select
            shipment_no as shipment_no --任务单号
             ,load_site_code --装车扫描网点code
             ,load_site_name --装车扫描网点名称
             ,next_station_code --运单目的卸车点code
             ,next_station_name --运单目的卸车点名称
             ,max(planned_departure_time) as datetime --运行日期
             ,max(shipment_name) as vehicleline_name --线路名称
             ,max(vehicleline_code) as vehicleline_code--线路编码
             ,max(load_simple_name || '-' ||  next_simple_name) as line_name --线路段
             ,max(shipstop_mileage) as total_mileage --线路段公里数
             ,max(case when business_attribute = 1 then '正班'
                             when business_attribute = 2 then '加班' end) as task_type --任务类型
             ,max(case when operation_model = 1 then '单边'
                             when operation_model = 2 then '对开'
                             when operation_model = 3 then '往返' end) as operation_model --运行模式
             ,max(plate_number) as plate_number --车牌号
             ,max(vehicletype_name) as vehicletype_name --车型
             ,max(load_site_planned_departure_time) as planned_departure_time --规划发车时间
             ,max(load_site_app_departure_time) as actual_departure_time --实际发车时间
             ,max(case when load_site_planned_departure_time >= load_site_app_departure_time then '是' else '否' end) as ispunctuality --是否准点
             ,max(next_site_planned_arrival_time) as planned_arrival_time --规划到达时间
             ,max(next_site_app_arrival_time) as actual_arrival_time --实际到达时间
             ,max(case when next_site_app_arrival_time >  next_site_planned_arrival_time then '是' else '否' end) as islate --是否晚点
             ,max(round(unix_timestamp(next_site_planned_arrival_time) - unix_timestamp(load_site_planned_departure_time)) /60) as plan_driving_time --规划运行时长
             ,max(round(unix_timestamp(next_site_app_arrival_time) - unix_timestamp(load_site_app_departure_time)) /60) as actual_driving_time --实际运行时长
             ,max(load_site_name || '-' || load_site_code) as start_center_name --出发转运中心名称
             ,max(next_station_name || '-' || next_station_code) as end_center_name --目的转运中心名称
             ,dt
    from jms_dwd.dwd_wide_tms_trunk_shipno_road_waybill_dt
    where dt between date_sub('{{ execution_date |  cst_ds }}',3) and ('{{ execution_date |  cst_ds }}')
      and shipment_state<> 5
    group by shipment_no,load_site_code,load_site_name,next_station_code,next_station_name,dt
) ship
    left join jms_dim.dim_lmdm_sys_network regional  --地区
              on regional.code = ship.load_site_code
    left join jms_dim.dim_lmdm_sys_network provider  --省
              on provider.code = ship.next_station_code
    distribute by dt;